package database;

import database.Profile.Status;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * A singleton service available at the business tier in order to insert and
 * retrieve data from a Java DB Derby database
 *
 */
public final class DataService {

    // DB connection URL specification includes the JDBC protocol, 
    // the server address and port, the database name that will be created if it does not exist
    // and the username and password
    private final String dbURL = "jdbc:derby://localhost:1527/MISS;create=true;user=sopra;password=adminadmin";
    //private final String dbURL = "jdbc:derby://localhost:12027/DB_RTA1;create=true;user=OIL1;password=OIL1";
    // jdbc Connection
    private Connection conn = null;
    private Statement stmt = null;
    private static DataService instance = null;

    DataService() {
        createConnection();
    }

    /**
     * Returns the service instance
     *
     * @return DataService already created of freshly instantiated
     */
    public static DataService getInstance() {
        if (instance == null) {
            instance = new DataService();
        }
        return instance;
    }

    private String createTableProfiles() throws SQLException {
        String r = "Table Profiles created!";
        try {
            // creates a SQL Statement object in order to execute the SQL insert command
            stmt = conn.createStatement();
            stmt.execute("CREATE TABLE Profiles (ProfileID int NOT NULL "
                    + "GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1 ) "
                    + "PRIMARY KEY, "
                    + "FirstName varchar(100) NOT NULL, "
                    + "LastName varchar(100) NOT NULL, "
                    + "Login varchar (100) NOT NULL, "
                    + "Password varchar(100) NOT NULL, "
                    + "Email   varchar(100), "
                    + "IsAdmin boolean DEFAULT false NOT NULL, "
                    + "Status int DEFAULT 0 NOT NULL,"
                    + "NbConnections int DEFAULT 0)");
            // insertion of the admin profile 
            stmt.execute("INSERT INTO Profiles (FirstName, LastName, Login, Password, IsAdmin)"
                    + "VALUES ('admin', 'admin', 'admin', 'adminadmin', true)");
            stmt.close();
        } catch (SQLException e) {
            System.err.println(e.toString());
            // checks if the table doesn't already exist
            if (!e.getSQLState().equalsIgnoreCase("X0Y32")) {
                return r;
            }

            throw e;
        }
        return r;
    }

    private String createTableFriends() throws SQLException {
        String r = "Table Friends created!";
        try {
            // creates a SQL Statement object in order to execute the SQL insert command
            stmt = conn.createStatement();
            stmt.execute("CREATE TABLE Friends ("
                    + "FriendshipID int NOT NULL GENERATED ALWAYS AS IDENTITY "
                    + "(START WITH 1, INCREMENT BY 1 ) PRIMARY KEY, "
                    + "Friend1 int NOT NULL, "
                    + "Friend2 int NOT NULL, "
                    + "IsAccepted boolean DEFAULT false NOT NULL, "
                    + "FOREIGN KEY (Friend1) REFERENCES Profiles(ProfileID), "
                    + "FOREIGN KEY (Friend2) REFERENCES Profiles(ProfileID) )");
            stmt.close();
            //System.out.println("Requête executée");
        } catch (SQLException e) {
            //System.err.println(e.toString());
            // checks if the table doesn't already exist
            if (!e.getSQLState().equalsIgnoreCase("X0Y32")) {
                return r;
            }
            r = e.toString();
            throw e;
        }
        return r;
    }

    private String createTableMessages() throws SQLException {
        String r = "Table Messages created!";
        try {
            // creates a SQL Statement object in order to execute the SQL insert command
            stmt = conn.createStatement();
            stmt.execute("CREATE TABLE Messages ("
                    + "MessageID int NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1 ) PRIMARY KEY, "
                    + "Sender int NOT NULL, "
                    + "Receiver int NOT NULL, "
                    + "Text varchar(300) NOT NULL, "
                    + "DateMsg DATE NOT NULL, "
                    + "TimeMsg TIME NOT NULL, "
                    + "IsDelivered boolean DEFAULT false NOT NULL,"
                    + "FOREIGN KEY (Sender) REFERENCES Profiles(ProfileID), "
                    + "FOREIGN KEY (Receiver) REFERENCES Profiles(ProfileID))");
            stmt.close();
        } catch (SQLException e) {
            System.err.println(e.toString());
            if (!e.getSQLState().equalsIgnoreCase("X0Y32")) {
                return r;
            }
            r = e.toString();
            throw e;
        }
        return r;
    }

    private String createTableFiles() throws SQLException {
        String r = "Table Files created!";
        try {
            // creates a SQL Statement object in order to execute the SQL insert command
            stmt = conn.createStatement();
            stmt.execute("CREATE TABLE Files ("
                    + "FileID int NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1 ) PRIMARY KEY, "
                    + "Filename varchar(100) NOT NULL, "
                    + "Size int NOT NULL, "
                    + "DateUpload DATE NOT NULL, "
                    + "TimeUpload TIME NOT NULL, "
                    + "Sender int NOT NULL, "
                    + "Receiver int NOT NULL, "
                    + "IsDelivered boolean DEFAULT false NOT NULL, "
                    + "FOREIGN KEY (Sender) REFERENCES Profiles(ProfileID), "
                    + "FOREIGN KEY (Receiver) REFERENCES Profiles(ProfileID))");
            stmt.close();
        } catch (SQLException e) {
            System.err.println(e.toString());
            if (!e.getSQLState().equalsIgnoreCase("X0Y32")) {
                return r;
            }
            r = e.toString();
            throw e;
        }
        return r;
    }

    /**
     * Establishment of the connection with the database server
     */
    private void createConnection() {
        try {
            //Get a connection based on the db URL
            System.out.println("Tentative de connexion à la BD");
            conn = DriverManager.getConnection(dbURL);
            if (!conn.getMetaData().getTables(null, null, "PROFILES", null).next()) {
                /*La table n'existe pas on la créée*/
                createTableProfiles();
            }
            if (!conn.getMetaData().getTables(null, null, "FRIENDS", null).next()) {
                /*La table n'existe pas on la créée*/
                createTableFriends();
            }
            if (!conn.getMetaData().getTables(null, null, "MESSAGES", null).next()) {
                /*La table n'existe pas on la créée*/
                createTableMessages();
            }
            if (!conn.getMetaData().getTables(null, null, "FILES", null).next()) {
                /*La table n'existe pas on la créée*/
                createTableFiles();
            }
        } catch (SQLException e) {
        }
    }

    public String insertProfile(Profile profile) throws SQLException {
        String r = "New profile inserted!";
        if (this.getProfileID(profile) == -1) {
            try {
                // creates a SQL Statement object in order to execute the SQL insert command
                stmt = conn.createStatement();
                //String login = (firstName, lastName, password)).createLogin();
                if (profile.getEmail() != null) {
                    stmt.execute("INSERT INTO Profiles (FirstName, LastName, Login, Password, Email, IsAdmin) "
                            + "VALUES ('" + profile.getFirstName() + "', '" + profile.getLastName() + "', "
                            + "'" + profile.getLogin() + "', '" + profile.getPassword() + "', "
                            + "'" + profile.getEmail() + "', " + profile.isAdmin() + ")");
                } else {
                    stmt.execute("INSERT INTO Profiles (FirstName, LastName, Login, Password, IsAdmin) "
                            + "VALUES ('" + profile.getFirstName() + "', '" + profile.getLastName() + "', "
                            + "'" + profile.getLogin() + "', '" + profile.getPassword() + "', "
                            + profile.isAdmin() + ") ");
                }
                stmt.close();
                //System.out.println("Requête executée");
            } catch (SQLException e) {
                //System.err.println(e.toString());
                r = e.toString();
                throw e;
            }
        }
        return r;
    }

    public String deleteProfile(Profile profile) throws SQLException {
        String r = "Profile deleted!";
        int profileID = this.getProfileID(profile);
        try {
            // creates a SQL Statement object in order to execute the SQL insert command
            stmt = conn.createStatement();
            stmt.execute("DELETE FROM Friends WHERE Friend1=" + profileID + " OR Friend2=" + profileID);
            stmt.execute("DELETE FROM Files WHERE Sender=" + profileID + " OR Receiver=" + profileID);
            stmt.execute("DELETE FROM Messages WHERE Sender=" + profileID + " OR Receiver=" + profileID);
            stmt.execute("DELETE FROM Profiles WHERE Login='" + profile.getLogin() + "'");
            stmt.close();
            //System.out.println("Requête executée");
        } catch (SQLException e) {
            //System.err.println(e.toString());
            r = e.toString();
            throw e;
        }
        return r;
    }

    public String setStatus(Profile profile, Profile.Status status) throws SQLException {
        String r = "Status modified!";
        try {
            // creates a SQL Statement object in order to execute the SQL insert command
            stmt = conn.createStatement();
            stmt.execute("UPDATE Profiles SET Status=" + status.getValue()
                    + " WHERE Login='" + profile.getLogin() + "'");
            stmt.close();
            //System.out.println("Requête executée");
        } catch (SQLException e) {
            //System.err.println(e.toString());
            r = e.toString();
            throw e;
        }
        return r;
    }

    public String setEmail(Profile profile, String email) throws SQLException {
        String r = "Email address modified!";
        try {
            // creates a SQL Statement object in order to execute the SQL insert command
            stmt = conn.createStatement();
            stmt.execute("UPDATE Profiles SET Email='" + email
                    + "' WHERE Login='" + profile.getLogin() + "'");
            stmt.close();
            //System.out.println("Requête executée");
        } catch (SQLException e) {
            //System.err.println(e.toString());
            r = e.toString();
            throw e;
        }
        return r;
    }

    public String setPassword(Profile profile, String password) throws SQLException {
        String r = "Email address modified!";
        try {
            // creates a SQL Statement object in order to execute the SQL insert command
            stmt = conn.createStatement();
            stmt.execute("UPDATE Profiles SET Password='" + password
                    + "' WHERE Login='" + profile.getLogin() + "'");
            stmt.close();
            //System.out.println("Requête executée");
        } catch (SQLException e) {
            //System.err.println(e.toString());
            r = e.toString();
            throw e;
        }
        return r;
    }

    public String selectAllProfiles() {
        String r = "";
        try {
            stmt = conn.createStatement();
            ResultSet results = stmt.executeQuery("SELECT * FROM Profiles");
            ResultSetMetaData rsmd = results.getMetaData();
            //int numberCols = rsmd.getColumnCount();
            for (int i = 2; i <= 4; i++) {
                //print Column Names
                r += rsmd.getColumnLabel(i) + "\t\t";
            }
            r += "\n<br>";
            while (results.next()) {
                String firstName = results.getString(2);
                String lastName = results.getString(3);
                String login = results.getString(4);
                r += firstName + "\t\t" + lastName + "\t\t" + login + "\n<br>";
            }
            results.close();
            stmt.close();
        } catch (SQLException sqlExcept) {
        }
        return r;
    }

    public String insertPendingFriendship(Profile friend1, Profile friend2) throws SQLException {
        String r = "New friendship inserted!";
        try {
            // creates a SQL Statement object in order to execute the SQL insert command
            stmt = conn.createStatement();
            stmt.execute("INSERT INTO Friends (Friend1,Friend2) VALUES "
                    + "((select ProfileID from Profiles where Login='" + friend1.getLogin()
                    + "') , (select ProfileID from Profiles where Login='" + friend2.getLogin() + "'))");
            stmt.close();
            //System.out.println("Requête executée");
        } catch (SQLException e) {
            //System.err.println(e.toString());
            r = e.toString();
            throw e;
        }
        return r;
    }

    public void acceptFriendship(Profile profile1, Profile profile2) {
        int profile1ID = this.getProfileID(profile1);
        int profile2ID = this.getProfileID(profile2);
        if (profile1ID != -1 && profile2ID != -1) { //Check if profile1 and profile2 exist
            try { // creates a SQL Statement object in order to execute the SQL insert command 
                stmt = conn.createStatement();
                stmt.execute(
                        "UPDATE Friends SET IsAccepted = true "
                        + " WHERE (Friend1=" + profile1ID + " AND Friend2=" + profile2ID + ")"
                        + " OR (Friend2=" + profile1ID + " AND Friend1=" + profile2ID + ")");
                stmt.close();
            } catch (SQLException e) {
                System.err.println(e.toString());
            }
        }
    }

    public ArrayList<String> getPendingFriendship(Profile profile) {
        ArrayList<String> logins = new ArrayList<String>();
        int profileID = this.getProfileID(profile);
        if (profileID != -1) {
            try {
                stmt = conn.createStatement();
                ResultSet results = stmt.executeQuery("SELECT DISTINCT Login FROM Profiles WHERE ProfileID IN ("
                        + "SELECT Friend1 FROM Friends WHERE IsAccepted = false AND Friend2=" + profileID + ")");
                while (results.next()) {
                    logins.add(results.getString(1));
                }
                results.close();
                stmt.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataService.class.getName()).log(Level.SEVERE, null, ex);
            }

        }
        return logins;
    }

    public String deleteFriendship(Profile friend1, Profile friend2) throws SQLException {
        String r = "Friendship deleted!";
        try {
            // creates a SQL Statement object in order to execute the SQL insert command
            stmt = conn.createStatement();
            stmt.execute("DELETE FROM Friends WHERE "
                    + "(Friend1 IN (SELECT ProfileID FROM Profiles "
                    + "WHERE Login='" + friend1.getLogin() + "') AND "
                    + "Friend2 IN (SELECT ProfileID FROM Profiles "
                    + "WHERE Login='" + friend2.getLogin() + "')) "
                    + "OR (Friend2 IN (SELECT ProfileID FROM Profiles "
                    + "WHERE Login='" + friend1.getLogin() + "') AND "
                    + "Friend1 IN (SELECT ProfileID FROM Profiles "
                    + "WHERE Login='" + friend2.getLogin() + "'))");
            stmt.close();
            //System.out.println("Requête executée");
        } catch (SQLException e) {
            //System.err.println(e.toString());
            r = e.toString();
            throw e;
        }
        return r;
    }

    public ArrayList<String> searchFriendByLogin(String search) {
        ArrayList<String> results = new ArrayList<>();
        ResultSet resultSet;
        try {
            // creates a SQL Statement object in order to execute the SQL insert command
            stmt = conn.createStatement();
            resultSet = stmt.executeQuery("SELECT Login FROM Profiles WHERE Login LIKE '%" + search.toLowerCase() + "%'");
            while (resultSet.next()) { // if result of the query is not empty 
                results.add(resultSet.getString(1));
            }
            stmt.close();
            //System.out.println("Requête executée");
        } catch (SQLException e) {
        }
        return results;
    }

    public ArrayList<String> searchFriendByEmail(String search) {
        ArrayList<String> results = new ArrayList<>();
        ResultSet resultSet;
        try {
            // creates a SQL Statement object in order to execute the SQL insert command
            stmt = conn.createStatement();
            resultSet = stmt.executeQuery("SELECT Login FROM Profiles WHERE Email LIKE '%" + search.toLowerCase() + "%'");
            while (resultSet.next()) { // if result of the query is not empty 
                results.add(resultSet.getString(1));
            }
            stmt.close();
            //System.out.println("Requête executée");
        } catch (SQLException e) {
        }
        return results;
    }

    public ArrayList<String> searchFriendByLastName(String search) {
        ArrayList<String> results = new ArrayList<>();
        ResultSet resultSet;
        try {
            // creates a SQL Statement object in order to execute the SQL insert command
            stmt = conn.createStatement();
            resultSet = stmt.executeQuery("SELECT Login FROM Profiles WHERE LastName LIKE '%" + search + "%'");
            while (resultSet.next()) { // if result of the query is not empty 
                results.add(resultSet.getString(1));
            }
            stmt.close();
            //System.out.println("Requête executée");
        } catch (SQLException e) {
        }
        return results;
    }

    public boolean correctPassword(String login, String password) {
        boolean res = false;
        try {
            // creates a SQL Statement object in order to execute the SQL insert command
            stmt = conn.createStatement();
            ResultSet result = stmt.executeQuery("SELECT * FROM Profiles WHERE "
                    + "Login='" + login + "' AND Password='" + password + "'");
            if (result.next()) { // if result of the query is not empty
                res = true;
            }
            stmt.close();

        } catch (SQLException e) {
            System.err.println(e.toString());
        }
        return res;
    }

    public boolean isAdmin(Profile profile) {
        boolean res = false;
        try {
            // creates a SQL Statement object in order to execute the SQL insert command
            stmt = conn.createStatement();
            ResultSet result = stmt.executeQuery("SELECT * FROM Profiles WHERE "
                    + "Login='" + profile.getLogin() + "' AND IsAdmin=" + true);
            if (result.next()) { // if result of the query is not empty
                res = true;
            }
            stmt.close();

        } catch (SQLException e) {
            System.err.println(e.toString());
        }
        return res;
    }

    public void setAdmin(Profile profile, boolean isAdmin) {
        try {
            // creates a SQL Statement object in order to execute the SQL insert command
            stmt = conn.createStatement();
            stmt.execute("UPDATE Profiles SET isAdmin = " + isAdmin + " WHERE Login='"
                    + profile.getLogin() + "'");
            stmt.close();

        } catch (SQLException e) {
            System.err.println(e.toString());
        }
    }

    public int getProfileID(Profile profile) {
        int res = -1; //-1 indique que le profile n'existe pas
        try {
            stmt = conn.createStatement();
            ResultSet result = stmt.executeQuery("SELECT PROFILEID FROM Profiles WHERE "
                    + "Login='" + profile.getLogin() + "'");
            if (result.next()) { // if result of the query is not empty
                //System.out.println("This profile exist !");
                //result.first(); //Take the first result because both profiles haven't the same login
                res = Integer.parseInt(result.getString(1)); //The first row is PROFILEID
                //System.out.println("ID of " + profile.getLogin() + " : " + res);
            } else {
                //System.out.println("WARNING : This profile doesn't exist ! -> return -1 value");
            }
            stmt.close();
        } catch (SQLException e) {
            System.err.println(e.toString());
        }
        return res;

    }

    public boolean isFriend(Profile p1, Profile p2) {
        boolean res = false;
        int p1ID = getProfileID(p1);
        int p2ID = getProfileID(p2);
        if (p1ID != -1 && p2ID != -1) { //Check if both profiles exist
            try {
                stmt = conn.createStatement();
                ResultSet result = stmt.executeQuery("SELECT * FROM FRIENDS WHERE "
                        + "(( FRIEND1=" + p1ID + " AND FRIEND2=" + p2ID + " ) "
                        + "OR (FRIEND2=" + p1ID + " AND FRIEND1=" + p2ID + " )) "
                        + "AND isAccepted = true");
                if (result.next()) { // if result of the query is not empty
                    res = true;
                }
                stmt.close();
            } catch (SQLException e) {
                System.err.println(e.toString());
            }
        }
        return res;

    }

    public String insertMessage(Message message) throws SQLException {
        String r = "New  message inserted!";

        if (isFriend(message.getSender(), message.getReceiver())) { //Check if the sender and receiver are friends
            try {
                // creates a SQL Statement object in order to execute the SQL insert command
                stmt = conn.createStatement();
                stmt.execute("INSERT INTO MESSAGES (Sender, Receiver, Text, DateMsg, TimeMsg, IsDelivered ) "
                        + "VALUES (" + getProfileID(message.getSender()) + ", "
                        + getProfileID(message.getReceiver()) + ", '"
                        + message.getText() + "' , '" + message.getDateMsg()
                        + "' , '" + message.getTimeMsg() + "'," + message.isDelivered() + ")");
                stmt.close();
                //System.out.println("Requête executée");
            } catch (SQLException e) {
                //System.err.println(e.toString());
                r = e.toString();
                throw e;
            }
            return r;
        } else {
            return "WARNING : Can't insert message in database : the sender and receiver are not friends !";
        }
    }

    public String insertFile(File file) throws SQLException {
        String newName = file.getName() + "_" + (this.getFileLastID() + 1);
        if (isFriend(file.getSender(), file.getReceiver()) && !existFile(file)) { //Check if the sender and receiver are friends
            try {
                // creates a SQL Statement object in order to execute the SQL insert command
                stmt = conn.createStatement();
                stmt.execute("INSERT INTO Files (Filename, Size, Sender, Receiver, DateUpload, TimeUpload ) "
                        + "VALUES ('" + newName + "', "
                        + (int) file.length() + ", "
                        + getProfileID(file.getSender()) + ", "
                        + getProfileID(file.getReceiver()) + ", '"
                        + file.getDateUpload()
                        + "' , '" + file.getTimeUpload() + "')");
                stmt.close();
                //System.out.println("Requête executée");
            } catch (SQLException e) {
                //System.err.println(e.toString());
                e.toString();
                throw e;
            }
            return newName;
        } else {
            return null;
        }
    }

    public boolean existFile(File file) {
        boolean res = false;
        int senderID = this.getProfileID(file.getSender());
        int receiverID = this.getProfileID(file.getReceiver());
        if (senderID != -1 && receiverID != -1) {
            try {
                // creates a SQL Statement object in order to execute the SQL insert command
                stmt = conn.createStatement();
                ResultSet result = stmt.executeQuery("SELECT * FROM FILES WHERE Size=" + (int) file.length() + " AND Sender=" + senderID + " AND Receiver=" + receiverID
                        + " AND Filename LIKE '" + file.getName() + "%'");
                if (result.next()) {
                    res = true;
                }
            } catch (SQLException ex) {
                Logger.getLogger(DataService.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return res;
    }

    public String deleteFile(File file) throws SQLException {
        String r = "File deleted!";
        try {
            // creates a SQL Statement object in order to execute the SQL insert command
            stmt = conn.createStatement();
            stmt.execute("DELETE FROM Files WHERE Filename='" + file.getName() + "'");
            stmt.close();
            //System.out.println("Requête executée");
        } catch (SQLException e) {
            //System.err.println(e.toString());
            r = e.toString();
            throw e;
        }
        return r;
    }

    public ArrayList<String> getOldFiles() {
        ArrayList<String> filesToRemove = new ArrayList<String>();
        try { // creates a SQL Statement object in order to execute the SQL insert command 
            stmt = conn.createStatement();
            ResultSet result = stmt.executeQuery(
                    "SELECT Filename FROM Files "
                    + "WHERE cast({fn timestampadd(SQL_TSI_DAY, -21, current_timestamp)} as Date ) > DateUpload");
            while (result.next()) { // if result of the query is not empty 
                filesToRemove.add(result.getString(1));
            }
            stmt.close();
        } catch (SQLException e) {
            System.err.println(e.toString());
        }
        return filesToRemove;
    }

    public ArrayList<File> getAllFiles(Profile profile) {
        ArrayList<File> files = new ArrayList<File>();
        try { // creates a SQL Statement object in order to execute the SQL insert command 
            stmt = conn.createStatement();
            ResultSet result = stmt.executeQuery(
                    "SELECT Filename, Sender, Receiver FROM Files WHERE Receiver IN (SELECT profileID FROM Profiles WHERE Login='" + profile.getLogin() + "')");
            while (result.next()) { // if result of the query is not empty 
                files.add(new File(result.getString(1), this.getProfile(result.getInt(2)), this.getProfile(result.getInt(3))));
            }
            stmt.close();
        } catch (SQLException e) {
            System.err.println(e.toString());
        }
        return files;
    }

    public ArrayList<File> getNotDownloadedFiles(Profile profile) {
        ArrayList<File> files = new ArrayList<File>();
        try { // creates a SQL Statement object in order to execute the SQL insert command 
            stmt = conn.createStatement();
            ResultSet result = stmt.executeQuery(
                    "SELECT Filename, Sender, Receiver FROM Files WHERE IsDelivered = false AND Receiver IN (SELECT profileID FROM Profiles WHERE Login='" + profile.getLogin() + "')");
            while (result.next()) { // if result of the query is not empty 
                files.add(new File(result.getString(1), this.getProfile(result.getInt(2)), this.getProfile(result.getInt(3))));
            }
            stmt.close();
        } catch (SQLException e) {
            System.err.println(e.toString());
        }
        return files;
    }

    public int getFileLastID() {
        int fileID = -1;
        try { // creates a SQL Statement object in order to execute the SQL insert command 
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet result = stmt.executeQuery(
                    "SELECT FileID FROM Files ORDER BY FileID");
            if (result.last()) { // if result of the query is not empty 
                fileID = result.getInt(1);
            } else {
                fileID = 0;
            }
            stmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return fileID;
    }

    public int getFileID(File file) {
        int fileID = -1;
        try { // creates a SQL Statement object in order to execute the SQL insert command 
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet result = stmt.executeQuery(
                    "SELECT FileID from FILES WHERE Filename LIKE '" + file.getName() + "%'");
            if (result.next()) { // if result of the query is not empty 
                fileID = result.getInt(1);
            }
            stmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return fileID;
    }

    public ArrayList<String> getContactList(Profile profile) {
        ArrayList<String> contacts = new ArrayList<String>();
        try { // creates a SQL Statement object in order to execute the SQL insert command 
            stmt = conn.createStatement();
            ResultSet result = stmt.executeQuery(
                    "SELECT login FROM Profiles WHERE ProfileID IN "
                    + "(SELECT DISTINCT Friend2 FROM Friends WHERE Friend1 IN "
                    + "(SELECT ProfileID FROM Profiles "
                    + "WHERE (Login='" + profile.getLogin()
                    + "' AND isAccepted=TRUE)) UNION "
                    + "(SELECT DISTINCT Friend1 FROM Friends "
                    + "WHERE Friend2 IN (SELECT ProfileID FROM Profiles "
                    + "WHERE (Login='" + profile.getLogin()
                    + "' AND isAccepted=TRUE))))");
            while (result.next()) { // if result of the query is not empty 
                contacts.add(result.getString(1));
            }
            stmt.close();
        } catch (SQLException e) {
            System.err.println(e.toString());
        }
        return contacts;
    }

    public ArrayList<Message> getAllConversation(Profile profile1, Profile profile2) {
        ArrayList<Message> conversation = new ArrayList<Message>();
        int profile1ID = this.getProfileID(profile1);
        int profile2ID = this.getProfileID(profile2);
        Profile sender = null;
        Profile receiver = null;
        if (profile1ID != -1 && profile2ID != -1) { //Check if profile1 and profile2 exist
            try { // creates a SQL Statement object in order to execute the SQL insert command 
                stmt = conn.createStatement();
                ResultSet result = stmt.executeQuery(
                        "(SELECT Profiles.Firstname, Profiles.Lastname, Profiles.Login, "
                        + "Profiles.password, "
                        + "Messages.Receiver, Messages.Text, "
                        + "Messages.DateMsg,Messages.TimeMsg, Messages.MessageID "
                        + "FROM Messages, Profiles "
                        + "WHERE  (Profiles.ProfileID = Messages.Sender "
                        + "AND Profiles.Login ='" + profile1.getLogin() + "') "
                        + "AND (Receiver IN (SELECT ProfileID FROM Profiles "
                        + "WHERE Login = '" + profile2.getLogin() + "'))) "
                        + "UNION (SELECT Profiles.Firstname, "
                        + "Profiles.Lastname, Profiles.Login, Profiles.password,  "
                        + "Messages.Receiver, Messages.Text, "
                        + "Messages.DateMsg,Messages.TimeMsg, Messages.MessageID "
                        + "FROM Messages, Profiles "
                        + "WHERE  (Profiles.ProfileID = Messages.Sender "
                        + "AND Profiles.Login ='" + profile2.getLogin() + "') "
                        + "AND (Receiver IN (SELECT ProfileID FROM Profiles "
                        + "WHERE Login = '" + profile1.getLogin() + "'))) "
                        + "ORDER BY DATEMSG, TIMEMSG, MESSAGEID");
                while (result.next()) { // if result of the query is not empty
                    if (result.getInt(5) == profile1ID) {
                        receiver = profile1;
                        sender = profile2;
                    } else if (result.getInt(5) == profile2ID) {
                        receiver = profile2;
                        sender = profile1;
                    } else {
                        return null;
                    }
                    conversation.add(new Message(sender, receiver, result.getString(6), result.getDate(7), result.getTime(8), true));
                }
                stmt.close();
            } catch (SQLException e) {
                System.err.println(e.toString());
            }
        }
        return conversation;
    }

    public ArrayList<Message> getUndeliveredConversation(Profile profile1, Profile profile2) {
        ArrayList<Message> conversation = new ArrayList<Message>();
        int profile1ID = this.getProfileID(profile1);
        int profile2ID = this.getProfileID(profile2);
        Profile sender = null;
        Profile receiver = null;
        if (profile1ID != -1 && profile2ID != -1) { //Check if profile1 and profile2 exist
            try { // creates a SQL Statement object in order to execute the SQL insert command 
                stmt = conn.createStatement();
                ResultSet result = stmt.executeQuery(
                        "(SELECT Profiles.Firstname, Profiles.Lastname, Profiles.Login, "
                        + "Profiles.password, "
                        + "Messages.Receiver, Messages.Text, "
                        + "Messages.DateMsg,Messages.TimeMsg, Messages.MessageID "
                        + "FROM Messages, Profiles "
                        + "WHERE  (Profiles.ProfileID = Messages.Sender "
                        + "AND Profiles.Login ='" + profile1.getLogin() + "'"
                        + "AND Messages.IsDelivered = false) "
                        + "AND (Receiver IN (SELECT ProfileID FROM Profiles "
                        + "WHERE Login = '" + profile2.getLogin() + "'))) "
                        + "UNION (SELECT Profiles.Firstname, "
                        + "Profiles.Lastname, Profiles.Login, Profiles.password,  "
                        + "Messages.Receiver, Messages.Text, "
                        + "Messages.DateMsg,Messages.TimeMsg, Messages.MessageID "
                        + "FROM Messages, Profiles "
                        + "WHERE  (Profiles.ProfileID = Messages.Sender "
                        + "AND Profiles.Login ='" + profile2.getLogin() + "'"
                        + "AND Messages.IsDelivered = false " + ") "
                        + "AND (Receiver IN (SELECT ProfileID FROM Profiles "
                        + "WHERE Login = '" + profile1.getLogin() + "'))) "
                        + "ORDER BY DATEMSG, TIMEMSG, MESSAGEID");
                while (result.next()) { // if result of the query is not empty
                    if (result.getInt(5) == profile1ID) {
                        receiver = profile1;
                        sender = profile2;
                    } else if (result.getInt(5) == profile2ID) {
                        receiver = profile2;
                        sender = profile1;
                    } else {
                        return null;
                    }
                    conversation.add(new Message(sender, receiver, result.getString(6), result.getDate(7), result.getTime(8), true));
                }
                stmt.close();
            } catch (SQLException e) {
                System.err.println(e.toString());
            }
        }
        return conversation;
        /*

         */
    }

    public void setViewedConversation(Profile profile1, Profile profile2) {
        int profile1ID = this.getProfileID(profile1);
        int profile2ID = this.getProfileID(profile2);
        if (profile1ID != -1 && profile2ID != -1) { //Check if profile1 and profile2 exist
            try { // creates a SQL Statement object in order to execute the SQL insert command 
                stmt = conn.createStatement();
                stmt.execute(
                        "UPDATE Messages SET IsDelivered = true "
                        + " WHERE MessageID IN "
                        + "((SELECT Messages.MessageID "
                        + "FROM Messages, Profiles "
                        + "WHERE  (Profiles.ProfileID = Messages.Sender "
                        + "AND Profiles.Login ='" + profile1.getLogin() + "'"
                        + "AND Messages.IsDelivered = false" + ") "
                        + "AND (Receiver IN (SELECT ProfileID FROM Profiles "
                        + "WHERE Login = '" + profile2.getLogin() + "'))) "
                        + "UNION (SELECT Messages.MessageID "
                        + "FROM Messages, Profiles "
                        + "WHERE  (Profiles.ProfileID = Messages.Sender "
                        + "AND Profiles.Login ='" + profile2.getLogin() + "'"
                        + "AND Messages.IsDelivered = false " + ") "
                        + "AND (Receiver IN (SELECT ProfileID FROM Profiles "
                        + "WHERE Login = '" + profile1.getLogin() + "'))) "
                        + "ORDER BY MESSAGEID)");
                stmt.close();
            } catch (SQLException e) {
                System.err.println(e.toString());
            }
        }
    }

    public void setFileDownloaded(File file) {

        try { // creates a SQL Statement object in order to execute the SQL insert command 
            stmt = conn.createStatement();
            stmt.execute("UPDATE Files SET IsDelivered = true "
                    + " WHERE Filename LIKE '" + file.getName() + "%'");
            stmt.close();
        } catch (SQLException e) {
            System.err.println(e.toString());
        }
    }
    
    public void setAllFilesDownloaded(Profile profile) {
        int profileID = getProfileID(profile);
        try { // creates a SQL Statement object in order to execute the SQL insert command 
            stmt = conn.createStatement();
            stmt.execute("UPDATE Files SET IsDelivered = true "
                    + " WHERE Receiver=" + profileID);
            stmt.close();
        } catch (SQLException e) {
            System.err.println(e.toString());
        }
    }

    public Profile getProfile(String login) { //Return NULL if not exist
        Profile profile = null;
        try { // creates a SQL Statement object in order to execute the SQL insert command 
            stmt = conn.createStatement();
            ResultSet result = stmt.executeQuery(
                    "SELECT * FROM PROFILES WHERE LOGIN='" + login + "'");
            if (result.next()) { // if result of the query is not empty 
                profile = new Profile(result.getString(2), result.getString(3), result.getString(5), result.getBoolean(7));
                profile.setLogin(result.getString(4));
                profile.setStatus(Status.convertValue(result.getInt(8)));
                if (result.getString(6) != null) {
                    profile.setEmail(result.getString(6));
                }

            }
            stmt.close();

        } catch (SQLException e) {
            System.err.println(e.toString());
        }
        return profile;
    }

    public Profile getProfile(int profileID) {
        Profile profile = null;
        try { // creates a SQL Statement object in order to execute the SQL insert command 
            stmt = conn.createStatement();
            ResultSet result = stmt.executeQuery(
                    "SELECT * FROM PROFILES WHERE ProfileID=" + profileID + "");
            if (result.next()) { // if result of the query is not empty 
                profile = new Profile(result.getString(2), result.getString(3), result.getString(5), result.getBoolean(7));
                profile.setLogin(result.getString(4));
                profile.setStatus(Status.convertValue(result.getInt(8)));
                if (result.getString(6) != null) {
                    profile.setEmail(result.getString(6));
                }

            }
            stmt.close();

        } catch (SQLException e) {
            System.err.println(e.toString());
        }
        return profile;
    }

    public void dropAllTables() {
        try { // creates a SQL Statement object in order to execute the SQL insert command 
            stmt = conn.createStatement();
            stmt.execute("DROP TABLE Friends");
            stmt.execute("DROP TABLE Files");
            stmt.execute("DROP TABLE Messages");
            stmt.execute("DROP TABLE Profiles");
        } catch (SQLException ex) {
            Logger.getLogger(DataService.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public int getNbMessages() {
        int count = 0;
        try { // creates a SQL Statement object in order to execute the SQL insert command 
            stmt = conn.createStatement();
            ResultSet result = stmt.executeQuery("SELECT COUNT(MessageID) FROM Messages");
            if (result.next()) { // if result of the query is not empty 
                count = result.getInt(1);
            }
            stmt.close();

        } catch (SQLException e) {
            System.err.println(e.toString());
        }
        return count;
    }

    public int getNbProfiles() {
        int count = 0;
        try { // creates a SQL Statement object in order to execute the SQL insert command 
            stmt = conn.createStatement();
            ResultSet result = stmt.executeQuery("SELECT COUNT(ProfileID) FROM Profiles");
            if (result.next()) { // if result of the query is not empty 
                count = result.getInt(1);
            }
            stmt.close();

        } catch (SQLException e) {
            System.err.println(e.toString());
        }
        return count;
    }
    
    public int getNbFiles() {
        int count = 0;
        try { // creates a SQL Statement object in order to execute the SQL insert command 
            stmt = conn.createStatement();
            ResultSet result = stmt.executeQuery("SELECT COUNT(FileID) FROM Files");
            if (result.next()) { // if result of the query is not empty 
                count = result.getInt(1);
            }
            stmt.close();

        } catch (SQLException e) {
            System.err.println(e.toString());
        }
        return count;
    }
    
    public int getNbConnections() {
        int count = 0;
        try { // creates a SQL Statement object in order to execute the SQL insert command 
            stmt = conn.createStatement();
            ResultSet result = stmt.executeQuery("SELECT SUM(NbConnections) AS SumConnections FROM Profiles");
            if (result.next()) { // if result of the query is not empty 
                count = result.getInt(1);
            }
            stmt.close();

        } catch (SQLException e) {
            System.err.println(e.toString());
        }
        return count;
    }

    public void incrementNbConnections(Profile profile) {
        try {
            // creates a SQL Statement object in order to execute the SQL insert command
            stmt = conn.createStatement();
            stmt.execute("UPDATE Profiles SET NbConnections=(SELECT NbConnections FROM Profiles "
                    + "WHERE Login='" + profile.getLogin() + "')+1 WHERE Login='" + profile.getLogin() + "'"
                    + "");
            stmt.close();
            //System.out.println("Requête executée");
        } catch (SQLException e) {
        }
    }

}
